package qit.itmiaojie.dao.impl;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import qit.itmiaojie.dao.UserDao;
import qit.itmiaojie.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;


public class UserDaoImpl implements UserDao {
    private JdbcTemplate jdbcTemplate;
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    @Override
    public User findByUsernameAndPassword(String username, String password) throws EmptyResultDataAccessException {
        User user = jdbcTemplate.queryForObject("select * from sys_user where username = ? and password = ?", new BeanPropertyRowMapper<>(User.class), username, password);
        return user;
    }

    /**
     * 查询所有用户
     * @return
     */
    @Override
    public List<User> findAll() {
        List<User> users = jdbcTemplate.query("select * from sys_user", new BeanPropertyRowMapper<User>(User.class));

        return users;
    }

    /**
     * 保存user 返回id
     * @param user
     * @return
     */
    @Override
    public Long saveUser(User user) {
        PreparedStatementCreator creator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement("insert into sys_user values(?,?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
               // PreparedStatement preparedStatement = connection.prepareStatement("insert into sys_user values(?,?,?,?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
                preparedStatement.setObject(1,null);
                preparedStatement.setString(2,user.getUsername());
                preparedStatement.setString(3,user.getEmail());
                preparedStatement.setString(4,user.getPassword());
                preparedStatement.setString(5,user.getPhoneNum());
                return preparedStatement;
            }
        };

        //创建keyHolder
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(creator,keyHolder);
        //获得生成的主键
        long userId = keyHolder.getKey().longValue();
        return userId; //返回当前保存用户的id 该id是数据库自动生成的
    }

    /**
     * 保存两表之间的关系 放在第三章表 sys_user_role 里
     * @param id
     * @param roleIds
     */
    @Override
    public void saveUIdAndRId(Long id, long[] roleIds) {
        for (long roleId : roleIds) {
            jdbcTemplate.update("insert into sys_user_role values (?,?)",id,roleId);
        }
    }

    /**
     * 删除两张表之间的关系
     */
    @Override
    public void delUserAndRoleTable(long id) {
        jdbcTemplate.update("DELETE FROM sys_user_role  WHERE sys_user_role.`userId` = ? ",id);
    }

    /**
     * 删除用户表
     * @param id
     */
    @Override
    public void delUser(Long id) {
        jdbcTemplate.update("delete  from sys_user where  id = ?",id);
    }


}
